package cn.saltybe.analysis.analyze;

import cn.saltybe.analysis.service.RentingHouseAveragePriceStatisticServiceImpl;
import cn.saltybe.analysis.util.RemoteExecute;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Component
public class ShareRentingHouseAnalyze {
    private static RentingHouseAveragePriceStatisticServiceImpl rentingHouseAveragePriceStatisticService;

    @Autowired
    public void setRentingHouseService(RentingHouseAveragePriceStatisticServiceImpl rentingHouseAveragePriceStatisticService) {
        this.rentingHouseAveragePriceStatisticService = rentingHouseAveragePriceStatisticService;
    }

    private static String datasourceUrl;
    private static String datasourceUsername;
    private static String datasourcePassword;

    @Value("${spring.datasource.url}")
    public void setDatasourceUrl(String datasourceUrl) {
        this.datasourceUrl = datasourceUrl;
    }

    @Value("${spring.datasource.username}")
    public void setDatasourceUsername(String datasourceUsername) {
        this.datasourceUsername = datasourceUsername;
    }

    @Value("${spring.datasource.password}")
    public void setDatasourcePassword(String datasourcePassword) {
        this.datasourcePassword = datasourcePassword;
    }

    public static void importData() throws Exception {
        System.out.println("* 启动 Sqoop 导入程序，将 租房信息 导入到 Hive 中");
        RemoteExecute re = RemoteExecute.getInstance();
        String out;
        String command = "sqoop import " +
                "--connect \"" + datasourceUrl + "\"" + " " +
                "--username " + datasourceUsername + " " +
                "--password " + datasourcePassword + " " +
                "--table " + "renting_house" + " " +
                "--hive-import" + " " +
                "--hive-table " + "renting_house" + " " +
                "--delete-target-dir" + " " +
                "-m " + "1";
        out = re.execute(command);
        System.out.println(out);
        System.out.println("执行完毕。");
    }

    public static void analyzeData() throws Exception {
        System.out.println("* 执行 Hive 语句，对 合租房信息 进行分析");
        RemoteExecute re = RemoteExecute.getInstance();
        String out;
        String command = "hive -e " +
                "'" +
                "CREATE EXTERNAL TABLE IF NOT EXISTS share_renting_house_average_price (" +
                "statistic_time STRING," +
                "city STRING," +
                "district STRING," +
                "avg_price DOUBLE" +
                ") " +
                "ROW FORMAT DELIMITED " +
                "FIELDS TERMINATED BY \"\\t\" " +
                "LINES TERMINATED BY \"\\n\"" +
                "LOCATION \"/hive/share_renting_house_average_price\"; " +
                "INSERT OVERWRITE TABLE share_renting_house_average_price " +
                "SELECT from_unixtime(unix_timestamp(), \"yyyy-MM-dd HH:mm:ss\") as statistic_time, city, district, avg(price / square) as avg_price " +
                "FROM renting_house WHERE rent_type = \"share\" GROUP BY city, district;" +
                "'";
        out = re.execute(command);
        System.out.println(out);
        System.out.println("执行完毕。");
    }

    public static void exportData() throws Exception {
        System.out.println("* 启动 Sqoop 导出程序，将 各地合租房平均价格 导出到 MySQL 中");
        rentingHouseAveragePriceStatisticService.dropTable("share");
        rentingHouseAveragePriceStatisticService.createTable("share");
        RemoteExecute re = RemoteExecute.getInstance();
        String out;
        String command = "sqoop export" + " \\" +
                "--connect \"" + datasourceUrl + "\"" + " \\" +
                "--username " + datasourceUsername + " \\" +
                "--password " + datasourcePassword + " \\" +
                "--table " + "share_renting_house_average_price" + " \\" +
                "--input-fields-terminated-by " + "'\\t'" + " \\" +
                "--export-dir " + "/hive/share_renting_house_average_price" + " \\" +
                "-m " + "1";
        out = re.execute(command);
        System.out.println(out);
        System.out.println("执行完毕。");
    }
}
